CREATE proc [dbo].[TeamCapacityCurrent] @ProjectNodeGUID nvarchar(255),@IterationSK int
as begin
  
 Create TABLE #Capacitytable 
(
TeamName nvarchar(300),
 Iteration nvarchar(300),
TotalCapacity float,
Duration int
)
Declare @Team nvarchar(300)
Declare @IterationPath nvarchar(300)
Declare @Capacity float
Declare @Duration int
Declare @LocalTeam nvarchar(300)

Declare Capacity_cursor CURSOR for
select TF.TeamFieldValue as 'Team Name',IT.IterationPath,
Sum(TC.Capacity)*(SELECT(DATEDIFF(dd, GETDATE(), FinishDate) + 1)-(DATEDIFF(wk, GETDATE(), FinishDate) * 2)-(CASE WHEN DATENAME(dw, GETDATE()) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, FinishDate) = 'Saturday' THEN 1 ELSE 0 END)) as 'Total Capacity',
(SELECT(DATEDIFF(dd, GETDATE(), FinishDate) + 1)-(DATEDIFF(wk, GETDATE(), FinishDate) * 2)-(CASE WHEN DATENAME(dw, GETDATE()) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, FinishDate) = 'Saturday' THEN 1 ELSE 0 END)) as 'Work Days Reamining'
from tbl_TeamConfigurationTeamFields TF
inner join tbl_TeamConfigurationCapacity  TC on TF.TeamId=TC.TeamId
inner join  [Tfs_Warehouse].[dbo].[DimIteration] IT on TC.IterationId=IT.IterationGUID 
where IT.ProjectGUID!='NULL' and IT.ProjectGUID=@ProjectNodeGUID and IT.IterationSK = @IterationSK
group by TF.TeamFieldValue,FinishDate,IT.IterationPath
having Sum(TC.Capacity)*(SELECT(DATEDIFF(dd, GETDATE(), FinishDate) + 1)-(DATEDIFF(wk, GETDATE(), FinishDate) * 2)-(CASE WHEN DATENAME(dw, GETDATE()) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, FinishDate) = 'Saturday' THEN 1 ELSE 0 END))>0


Open Capacity_cursor
Fetch next from Capacity_cursor into @Team,@IterationPath,@Capacity,@Duration
While @@FETCH_STATUS = 0
Begin

SET @LocalTeam=(SELECT top 1 team.TeamFieldValue
FROM tbl_TeamConfigurationCapacityDaysOffRange Tc inner join
tbl_TeamConfigurationTeamFields team on TC.TeamId=team.TeamId inner join
[Tfs_Warehouse].[dbo].[DimIteration] iter on TC.IterationId=iter.IterationGUID
inner join tbl_TeamConfigurationCapacity  TCa on TC.TeamMemberId=TCa.TeamMemberId
 where iter.ProjectGUID!='NULL' and iter.ProjectGUID=@ProjectNodeGUID and iter.IterationSK = @IterationSK and TC.IterationId=TCa.IterationId and team.TeamFieldValue=@Team
 group by [EndTime],team.TeamFieldValue,Tca.Capacity,TC.TeamMemberId
  having ((DATEDIFF(day,GETDATE(),[EndTime])+1) )>0)
 
  if(@Team=@LocalTeam)
begin
Insert into #Capacitytable(TeamName,Iteration,TotalCapacity,Duration )values (@Team,@IterationPath,@Capacity-(  select sum(NoOfDaysOff) from (
 SELECT Tca.Capacity*(SELECT(DATEDIFF(dd, GETDATE(), [EndTime]) + 1)-(DATEDIFF(wk, GETDATE(), [EndTime]) * 2)-(CASE WHEN DATENAME(dw, GETDATE()) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, [EndTime]) = 'Saturday' THEN 1 ELSE 0 END)) as 'NoOfDaysOff'
FROM tbl_TeamConfigurationCapacityDaysOffRange Tc inner join
tbl_TeamConfigurationTeamFields team on TC.TeamId=team.TeamId inner join
[Tfs_Warehouse].[dbo].[DimIteration] iter on TC.IterationId=iter.IterationGUID
inner join tbl_TeamConfigurationCapacity  TCa on TC.TeamMemberId=TCa.TeamMemberId
 where iter.ProjectGUID!='NULL' and iter.ProjectGUID=@ProjectNodeGUID and iter.IterationSK = @IterationSK and TC.IterationId=TCa.IterationId and TC.TeamId=TCa.TeamId  and team.TeamFieldValue=@Team
 )emp),@Duration)
	print'1';
	end
	else
	begin
	Insert into #Capacitytable(TeamName,Iteration,TotalCapacity,Duration )values (@Team,@IterationPath,@Capacity,@Duration)
	print'2';
	end
Fetch next from Capacity_cursor into @Team,@IterationPath,@Capacity,@Duration
End
Close Capacity_cursor
DEALLOCATE Capacity_cursor
select * from #Capacitytable
drop table #Capacitytable

  end
